package de.axiscars;

import java.sql.ResultSet;
import java.sql.SQLException;
import de.axiscars.Database;

/**
 * This class includes all methods of the webservice. 
 * 
 * @author Sabrina Schwarze
 * @author Ramiz Loki
 * @author Hendrik Bielefeld
 *
 */

public class CarService {
	// Creating a variable for the Statement and ResultSet
	private Database db = new Database();
	private ResultSet rs = null;
   
	/**
	 * This method returns all stations in an array of object Station that includes all properties of a rental station.
	 * 
	 * @return Array of object Station that includes all properties of a rental station.
	 */
   public Station[] getStations() {
	   try {
			//do SQL-Query
		   	rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_station;");
			
			//count the number of data records
			int records = 0;
			while (rs.next()) {
				records++;
			}
			
			//write ResultSet in object array
			Station station[] = new Station[records];
			rs.beforeFirst();
			int i=0;			
			while (rs.next()) {
				station[i] = new Station(Integer.valueOf(rs.getString("id")), rs.getString("zip_code"), rs.getString("city"), rs.getString("street"), rs.getString("street_nr"), rs.getString("phone"), rs.getString("email"));
				i++;
			}
			
			//return the result
			return station;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
   }
   
   /**
    * This method returns an object Station that includes all properties of a rental station. The given parameter station_id defines the station that should be returned.
    * 
    * @param station_id The id of the station that should be returned.
    * @return An object Station that includes all properties of the station.
    */
   public Station getStationById(int station_id) {
	   try {			
			//do SQL-Query
		    rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_station WHERE id = " + station_id + ";");
			
		    //jump to first data record in ResultSet
		    rs.next();
		    
		    //write ResultSet in object
			Station station = new Station(Integer.valueOf(rs.getString("id")), rs.getString("zip_code"), rs.getString("city"), rs.getString("street"), rs.getString("street_nr"), rs.getString("phone"), rs.getString("email"));
			
			//return the result
			return station;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
   }
   
   /**
	* This method returns all cars in an array of object Car that includes all properties of a car.
	* 
    * @return All cars in an array of object Car that includes all properties of a car.
    */
   public Car[] getCars() {
	   try {			
			//execute SQL statement
		    rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_car INNER JOIN tbl_station ON tbl_car.station_id = tbl_station.id;");
			
		    //count the number of data records
		    int records = 0;
			while (rs.next()) {
				records++;
			}
			
			//write ResultSet in object array
			Car car[] = new Car[records];
			rs.beforeFirst();
			int i=0;
			while (rs.next()) {
				car[i] = new Car(
							Integer.valueOf(rs.getString("tbl_car.id")),
							new Station(
									Integer.valueOf(rs.getString("tbl_car.station_id")),
									rs.getString("tbl_station.zip_code"),
									rs.getString("tbl_station.city"),
									rs.getString("tbl_station.street"),
									rs.getString("tbl_station.street_nr"),
									rs.getString("tbl_station.phone"),
									rs.getString("tbl_station.email")),
							rs.getString("tbl_car.brand"),
							rs.getString("tbl_car.model"),
							rs.getBytes("tbl_car.photo"),
							rs.getString("tbl_car.type"),
							Float.valueOf(rs.getString("tbl_car.price_per_day")),
							Integer.valueOf(rs.getString("tbl_car.minimum_age")),
							Integer.valueOf(rs.getString("tbl_car.number_seats")),
							Integer.valueOf(rs.getString("tbl_car.number_doors")), 
							rs.getString("tbl_car.gear_type"),
							rs.getString("tbl_car.air_conditioner"),
							Boolean.valueOf(rs.getString("stbl_car.tate")));
				i++;
			}
			
			//return the result
			return car;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
   }
   
   /**
    * This method returns all available cars at a defined station and in a given period of time. The cars will be returned in an array of object Car that includes all properties of a car.
    * 
    * @param station_id The station where the customer wants to pick up the car.
    * @param start_date The start date of the desired rental.
    * @param end_date The end date of the desired rental.
    * @return All available cars in an array of object Car that includes all properties of a car. 
    */
   public Car[] showAvailableCars(int station_id, String start_date, String end_date) {
	   try {			
			//execute SQL statement
		    rs = db.connect().createStatement().executeQuery("SELECT DISTINCT C.* " +
															 "FROM tbl_car LEFT JOIN tbl_rental ON tbl_car.id = tbl_rental.car_id " +
															 "INNER JOIN tbl_station ON tbl_car.station_id = tbl_station.id " +
															 "WHERE	tbl_car.state = 1 AND tbl_car.station_id = " + station_id + " " +
															 "AND (('" + start_date + "' NOT BETWEEN tbl_rental.start_date AND tbl_rental.end_date AND '" + end_date + "' NOT BETWEEN tbl_rental.start_date AND tbl_rental.end_date) OR (tbl_rental.start_date IS NULL AND tbl_rental.end_date IS NULL));");
			
			
		    //count the number of data records
		    int records = 0;
			while (rs.next()) {
				records++;
			}
			
			//write ResultSet in onject array
			Car car[] = new Car[records];
			rs.beforeFirst();			
			int i=0;
			while (rs.next()) {
				car[i] = new Car(
							Integer.valueOf(rs.getString("tbl_car.id")), 
							new Station(
									Integer.valueOf(rs.getString("tbl_car.station_id")),
									rs.getString("tbl_station.zip_code"),
									rs.getString("tbl_station.city"),
									rs.getString("tbl_station.street"),
									rs.getString("tbl_station.street_nr"),
									rs.getString("tbl_station.phone"),
									rs.getString("tbl_station.email")),
							rs.getString("tbl_car.brand"),
							rs.getString("tbl_car.model"),
							rs.getBytes("tbl_car.photo"),
							rs.getString("tbl_car.type"),
							Float.valueOf(rs.getString("tbl_car.price_per_day")),
							Integer.valueOf(rs.getString("tbl_car.minimum_age")),
							Integer.valueOf(rs.getString("tbl_car.number_seats")),
							Integer.valueOf(rs.getString("tbl_car.number_doors")), 
							rs.getString("tbl_car.gear_type"),
							rs.getString("tbl_car.air_conditioner"),
							Boolean.valueOf(rs.getString("tbl_car.state")));
				i++;
			}

			//return the result
			return car;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
   }
   
   /**
    * This method returns an object Car that includes all properties of a rental station. The given parameter id defines the car that should be returned.
    *
    * @param id The id of the car that should be returned
    * @return An object Car that includes all properties of a car.
    */
   public Car getCarDetails(int id) {
	   try {
			//do sql query
		    rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_car INNER JOIN tbl_station ON tbl_car.station_id = tbl_station.id  WHERE tbl_car.state = 1 AND tbl_car.id = " + id + ";");
			
		    //write ResultSet in object
			rs.next();
			Car car = new Car(
				Integer.valueOf(rs.getString("tbl_car.id")), 
				new Station(
						Integer.valueOf(rs.getString("tbl_car.station_id")),
						rs.getString("tbl_station.zip_code"),
						rs.getString("tbl_station.city"),
						rs.getString("tbl_station.street"),
						rs.getString("tbl_station.street_nr"),
						rs.getString("tbl_station.phone"),
						rs.getString("tbl_station.email")),
				rs.getString("tbl_car.brand"),
				rs.getString("tbl_car.model"),
				rs.getBytes("tbl_car.photo"),
				rs.getString("tbl_car.type"),
				Float.valueOf(rs.getString("tbl_car.price_per_day")),
				Integer.valueOf(rs.getString("tbl_car.minimum_age")),
				Integer.valueOf(rs.getString("tbl_car.number_seats")),
				Integer.valueOf(rs.getString("tbl_car.number_doors")), 
				rs.getString("tbl_car.gear_type"),
				rs.getString("tbl_car.air_conditioner"),
				Boolean.valueOf(rs.getString("tbl_car.state"))
			);
			
			//return the result
			return car;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
    }
   
   /**
    * This method calculates and returns the average rate of a specific car.
    * 
    * @param id The id of the car the average rate should be calculated of
    * @return The rounded average rate of the car.
    */
   public int getAverageRate(int id){
	   try {
			//do sql query
		    rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_rental WHERE state = 1 AND car_id = " + id + ";");
			
		    //count the number of data records
		    int records = 0;
			while (rs.next()) {
				records++;
			}
			
			//calculate the sum of all ratings
			rs.beforeFirst();
			float sum=0;
			while (rs.next()) {				
				sum = sum + rs.getInt("rate");
			}
			
			//divide the sum with the number of data records to get the average rate
			int rate = Math.round(sum/records);
			
			//return the rounded average rate
			return rate;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return 0;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
   }
   
   /**
    * This method returns an object Customer that includes all properties of a customer. The given parameter id defines the customer that should be returned.
    *
    * @param customer_id The id of the customer that should be returned
    * @return An object Customer that includes all properties of the station.
    */
   public Customer getCustomerDetails(int customer_id) {
	   try {
		   //do sql query
		   rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_customer WHERE state = 1 AND id = " + customer_id + ";");
		   
		   //write ResultSet in object
		   rs.next();
		   Customer customer = new Customer(rs.getInt("id"), rs.getString("name"), rs.getString("surname"), rs.getString("title"), rs.getString("phone"), rs.getString("email"), rs.getString("birthday"), rs.getString("state"), rs.getString("password"));
		   
		   //return the result
		   return customer;
	   } catch (Exception e) {
		   e.printStackTrace();
		   return null;
	   }
   }
   
   
   /**
	 * Checks the given customer_id and password with the attributes in database.
	 * 
	 * @param customer_id The user which tries to log in.
	 * @param password The password to be checked if it is equal to the one in database
	 * @return True, if login was successful otherwise false.
	 */
   public boolean rentalLogin(String customer_id, String password) {
	   try {
			//do sql query
		    rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_customer WHERE state='1';");
			
		    //go through all data records
		    while (rs.next()) {
				
		    	//get the data record that matches to the given customer id
		    	if (customer_id.equals(rs.getString("id"))) {
					
		    		//check if given password matches with password from database
		    		if (password.equals(rs.getString("password"))) {
						return true;
					} else {
						return false;
					}
				}
			}
		    
		    //return false if no customer with given id can be found
			return false;
		} catch (SQLException e) {
			System.out.println("Error Connecting: " + e + "");
		}
		return false;
    }
   
   /**
    * This method is used for register a new customer. It adds the given parameters into the database.
    * 
    * @param name The name of the new customer.
    * @param surname The surname of the new customer.
    * @param title The title of the new customer.
    * @param phone The phone number of the new customer.
    * @param email The email adress of the new customer.
    * @param birthday The birth date of the new customer.
    * @param password The password of the new customer.
    * @return True, if the registration was successful, otherwise false.
    */
   public boolean insertNewCustomer(String name, String surname, String title, String phone, String email, String birthday, String password) {
	   try {
			//execute sql statement
		    db.connect().createStatement().execute("INSERT INTO tbl_customer" +
					" VALUES ( NULL, '" + name + "', '" + surname + "', '" + title + "', '" + phone + "', '" + email + "', '" + birthday + "', 1,'"+ password +"');");
			
		    //return true if successful
		    return true;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return false;
	   }
    }
   
   /**
    * This method defines the customer id specified by the email address.
    * 
    * @param email The email address of the customer whose id should be returned.
    * @return The id of the customer.
    */
   public int getCustomerId(String email){
	   try {			
		    //do sql query
		    rs = db.connect().createStatement().executeQuery("SELECT * FROM tbl_customer WHERE email='" + email + "';");
			rs.next();
			//return the result
			return rs.getInt("id");
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return 0;
	   }
   }
   
   /**
    * This method is used for renting a car. It adds the given parameters into the database. 
    * 
    * @param car_id The id of the car that the customer wants to rent.
    * @param customer_id The id of the customer who wants to rent a car.
    * @param start_station The start station of the rental.
    * @param end_station The end station of the rental.
    * @param start_date The start date of the rental.
    * @param end_date The end date of the rental.
    * @return The rental id, if the rental was successful, otherwise 0.
    */
   public int insertNewRental(int car_id, int customer_id, int start_station, int end_station, String start_date, String end_date){
	   try {
		   	//Define a random rental id between 10000 and 99999
		   	int high = 99999;
		   	int low = 10000;
		   	int rental_id = (int) (Math.random() * (high - low) + low);
			
		   	//Insert values into database
		   	db.connect().createStatement().execute("INSERT INTO tbl_rental" +
					" VALUES ( " + rental_id +", " + car_id + ", " + customer_id + ", " + start_station + ", " + end_station + ", '" + start_date + "', '" + end_date + "', 1, NULL);");
			
		   	//Return the rental id
		   	return rental_id;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return 0;
		} finally {
			
		}
   }
   
   /**
    * This method is used for rating a car of a specific rental.
    * 
    * @param rental_id The rental that should be rated.
    * @param rate The grade (1-5) the customer rates the rental with.
    * @return True, if rating was successful, otherwise false.
    */
   public boolean rateCar(int rental_id, int rate){
	   try {
			//execute sql statement
		    db.connect().createStatement().execute("UPDATE tbl_rental SET rate = " + rate + " WHERE id= " + rental_id + ";");

		    //return true, if update was successful
		    return true;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return false;
	   } finally {
			
	   }
	   
   }
   
   /**
    * This method returns an Array of object Rental that includes all properties of a Rental. The given parameter customer_id defines the customer whose rentals should be returned.
    * @param customer_id The customer id whose rentals should be returned.
    * @return An array of object Car with all rentals of the given customer.
    */
   public Rental[] getUsersRentals(int customer_id){
	   try {			
			//do sql query
		    rs = db.connect().createStatement().executeQuery("SELECT * " +
															 "FROM tbl_rental INNER JOIN tbl_car ON tbl_rental.car_id = tbl_car.id INNER JOIN tbl_customer ON tbl_rental.customer_id = tbl_customer.customer_id " +
															 "WHERE tbl_rental.customer_id = " + customer_id + ";");
			//count the number of data records
		    int records = 0;
			while (rs.next()) {
				records++;
			}
			
			//write ResultSet in object array
			Rental rental[] = new Rental[records];
			rs.beforeFirst();
			int i=0;
			while (rs.next()) {
				rental[i] = new Rental(
						Integer.valueOf(rs.getString("tbl_rental.id")), 
						new Car(Integer.valueOf(rs.getString("tbl_rental.car_id")), new Station(), rs.getString("tbl_car.brand"), rs.getString("tbl_car.model"), rs.getBytes("tbl_car.photo"), rs.getString("tbl_car.type"), (float) 0.0, 0, 0, 0, "", rs.getString("tbl_car.air_conditioner"), false), 
						new Customer(),
						new Station(),
						new Station(),
						rs.getString("tbl_rental.start_date"),
						rs.getString("tbl_rental.end_date"), 
						rs.getString("tbl_rental.state"), 
						rs.getString("tbl_rental.rate") 
				);
				i++;
			}
			
			//return the result
			return rental;
	   } catch (Exception e) {
			// Could not connect to the database
		   e.printStackTrace();
		   return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
   }
}

